import openpyxl
from openpyxl.utils import get_column_letter,get_column_interval
import cv2

"""
该函数的作用：解析excel表格文件的结构信息
:param xml_read_path: 表格文件结构信息文件XMl文件存放地址
:return: 四个元素
All_cell_points：每个单元格在图像中的坐标信息
cells_adress：每个单元格的行列信息
sum(col_list)：图像中表格框的真实宽度
sum(height_list)：图像中表格框的真实高度
"""
# 读取excel
# excel_read_path = "D:\\program_donqi\\data\\0008\\0008.xlsx"
excel_read_path = "C:\\Users\\14197\\Desktop\\program_donqi\\data\\0007\\0007.xlsx"
wb = openpyxl.load_workbook(excel_read_path)
# print(wb.sheetnames)

# 读取excel文件第一张sheet
ws = wb[wb.sheetnames[0]]  # worksheet
# print(ws)

# 读取sheet表格行列数
max_row = ws.max_row  # 行
max_column = ws.max_column  # 列
# print(ws.max_row)
# print(ws.max_column)

# 去除空行
while True:
    # 行值初始化
    cell_value = None
    for i in range(1, max_column + 1):
        # 对最后一行行，从第一列到对最后一列遍历累计
        cell_value = ws.cell(max_row, i).value or cell_value
    # 查看左下角单元格边界是否有边框
    cell_border = ws.cell(row=max_row, column=1).border.right.border_style
    if cell_value is None and cell_border is None:
        max_row -= 1
    else:
        break

# 去除空列
while True:
    # 列值初始化
    cell_value = None
    # 对最后一列，从第一行到对最后一行遍历累计
    for i in range(1, max_row + 1):
        cell_value = ws.cell(i, max_column).value or cell_value
    # 查看右上角单元格边界是否有边框
    cell_border = ws.cell(row=1, column=max_column).border.right.border_style
    if cell_value is None and cell_border is None:
        max_column -= 1
    else:
        break




# print("行高")
# 读取行高
height_list = []
for i in range(1, max_row + 1):
    height = ws.row_dimensions[i].height
    # 如果为默认行高
    if height is None:
        height = ws.sheet_format.defaultRowHeight
    height_list.append(float(height))
    # print(height)

'''
print("列宽")
# 读取列宽
col_list = []
# 默认列宽
width = ws.sheet_format.baseColWidth + 1
for i in range(1, max_column + 1):
    # 判断该列宽度是否与上一列宽度相等,相等则取前一列的列宽，否则进行读取
    if ws.column_dimensions[get_column_letter(i)].max is None:
        col_list.append(float(width))
    else:
        width = ws.column_dimensions[get_column_letter(i)].width
        col_list.append(float(width))
    print(width)
'''

# 获取默认列宽
defaultColWidth = ws.sheet_format.defaultColWidth
# if defaultColWidth is None:
#     defaultColWidth = ws.sheet_format.baseColWidth+1

print("列宽")
# 创建定长空列表
col_list = [None for i in range(max_column)]
for i in ws.column_dimensions:
    # 判断该列是否属于表格内的列
    if i in get_column_interval(1,max_column):
        # 获取此列宽下的最大、小列索引
        min = ws.column_dimensions[i].min - 1
        max = ws.column_dimensions[i].max
        col_list[min:max] = [ws.column_dimensions[i].width] * (max - min)
# 为剩余列赋默认列宽
for i in range(len(col_list)):
    if col_list[i] is None:
        col_list[i] = defaultColWidth
# print(col_list)


# 以左上顶点为原点，各个单元的位置关系,向下、向右为正方向
# 存放单元格左上、右下顶点坐标
All_cell_points = []
for row in range(max_row):
    for column in range(max_column):
        height_end = sum(height_list[:row + 1])
        height_start = height_end - height_list[row]

        width_end = sum(col_list[:column + 1])
        width_start = width_end - col_list[column]

        cell_index = get_column_letter(column+1)+str(row+1)
        cell_points = [[height_start, width_start], [height_end, width_end], cell_index]
        All_cell_points.append(cell_points)

# 存放合并单元格的索引
cell_num = []
for merged_cell in ws.merged_cells.ranges:
    # 获取合并单元格的左上、右下单元格的表格索引
    left_col, left_row, right_col, right_row = merged_cell.bounds
    # 计算左上、右下单元格的坐标列表索引
    left_cell_num = (left_row - 1) * max_column + left_col - 1
    right_cell_num = (right_row - 1) * max_column + right_col - 1
    # 保留合并单元格左上右下角点坐标
    All_cell_points[left_cell_num][1] = All_cell_points[right_cell_num][1]

    #记录被合并的单元格的坐标索引
    for i in range(len(merged_cell.left)):
        for j in range(len(merged_cell.top)):
            cell_num.append(left_cell_num + i * max_column + j)
    # 去除起始单元格索引
    cell_num.remove(left_cell_num)
    # cell_num.append([left_cell_num, right_cell_num])

# 将要删除的单元格索引降序排列
cell_num.sort(reverse=True)
# 从后往前删除 单元格坐标列表 中  合并单元格坐标
for i in range(len(cell_num)):
    del All_cell_points[cell_num[i]]

print(All_cell_points)



# 将合并后的单元格角点可视化
img_origin = cv2.imread("C:\\Users\\14197\\Desktop\\program_donqi\\data\\0007\\0007_jiaozheng.jpg")
size = img_origin.shape
# print(size)
y = size[0]  # 图片垂直尺寸高y
x = size[1]  # 图片水平尺寸宽x
for i in range(len(All_cell_points)):
    cv2.circle(img_origin, (
    int(All_cell_points[i][0][1] / sum(col_list) * x), int(All_cell_points[i][0][0] / sum(height_list) * y)), 2,
               (255, 0, 0), 2)
    cv2.circle(img_origin, (
    int(All_cell_points[i][1][1] / sum(col_list) * x), int(All_cell_points[i][1][0] / sum(height_list) * y)), 2,
               (255, 0, 0), 2)
# img_origin = cv2.resize(img_origin, (int(x / 2), int(y / 2)))
cv2.imshow("img_origin", img_origin)
cv2.waitKey(0)
cv2.destroyAllWindows()
